<?php
include_once('Sqlexecutor.php');
include_once('TileUserInfo.php');
class Tile
{
    private $_dbh;
    private $_logger;

    public function __construct($dbh, $logger = Null)
    {/*{{{*/
        $this->_dbh = $dbh; 
        $this->_logger = $logger;
    }/*}}}*/

    public function getAllList()
    {/*{{{*/
        $sql = "select `id`, `number`, `length`, `width`, `texture`, `texture_color`, `class`, `class_color`, `vip`, `img`, `img_min`, `series_id` from bs_tile order by `insert_date` desc";
        try
        {
            $list = $this->_dbh->querys($sql);
            return $list;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getListBySeriesID($id = NULL)
    {/*{{{*/
        if($id === NULL)
        {
            $sql = "select `id`, `number`, `length`, `width`, `texture`, `texture_color`, `class`, `class_color`, `vip`, `img`, `img_min`, `series_id`, `insert_date` from bs_tile order by `insert_date` desc";
            $val = array();
        }
        else
        {
            $sql = "select `id`, `number`, `length`, `width`, `texture`, `texture_color`, `class`, `class_color`, `vip`, `img`, `img_min`, `series_id`, `insert_date` from bs_tile where series_id = ? order by `insert_date` desc";
            $val[] = $id;
        }
        try
        {
            $list = $this->_dbh->querys($sql, $val);
            return $list;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getListWithAvgPriceBySeriesID($id = NULL)
    {/*{{{*/
        $list = $this->getListBySeriesID($id);    
        if($list === false)
        {
            return false;
        }

        $o = new TileUserInfo($this->_dbh, $this->_logger);
        $price_arr = $o->getListOfAvgPriceBySeriesID($id);
        foreach($price_arr as $info)
        {
            $tile_id = $info['tile_id'];
            $avg_price = $info['avg_price'];
            $avg_score = $info['avg_score'];
            $price_map[$tile_id] = $avg_price;
            $score_map[$tile_id] = $avg_score;
        }
        $rst = array();
        foreach($list as $info)
        {
            $tile_id = $info['id'];
            $avg_price = isset($price_map[$tile_id]) ? $price_map[$tile_id] : 0;       
            $avg_score = isset($score_map[$tile_id]) ? $score_map[$tile_id] : 0;       
            $info['avg_price'] = sprintf("%.2f", $avg_price);
            $info['avg_score'] = sprintf("%.2f", $avg_score);
            $rst[] = $info;
        }

        return $rst;
    }/*}}}*/

    public function getInfoByID($id)
    {/*{{{*/
        $sql = "select `id`, `number`, `length`, `width`, `texture`, `texture_color`, `class`, `class_color`, `vip`, `img`, `img_min`, `series_id` from `bs_tile` where id = ?";
        $val[] = $id;
        try
        {
            $info = $this->_dbh->query($sql, $val);
            if($info === false)
                $info = NULL;
            return $info;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getInfoInIDS($ids)
    {/*{{{*/
        $sql = "select `id`, `number`, `length`, `width`, `texture`, `texture_color`, `class`, `class_color`, `vip`, `img`, `img_min`, `series_id` from `bs_tile` where id in ($ids)";
        try
        {
            $info = $this->_dbh->querys($sql);
            return $info;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function createTile($number, $length, $width, $texture, $texture_color, $class, $class_color, $vip, $img, $img_min, $series_id)
    {/*{{{*/
        $sql = "insert into bs_tile set `number` = ?, `length` = ?, `width` = ?, `texture` = ?, `texture_color` = ?, `class` = ?, `class_color` = ?, `vip` = ?, `img` = ?, `img_min` = ?, `series_id` = ?, `insert_date` = now()";
        $val[] = $number;
        $val[] = $length;
        $val[] = $width;
        $val[] = $texture;
        $val[] = $texture_color;
        $val[] = $class;
        $val[] = $class_color;
        $val[] = $vip;
        $val[] = $img;
        $val[] = $img_min;
        $val[] = $series_id;
        try
        {
            $this->_dbh->beginTrans();
            $this->_dbh->execute($sql, $val); 
            $lastId = $this->_dbh->getLastInsertID();
            $this->_dbh->commit();

            return $lastId;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            if($e->errorInfo[1] == 1062 && $e->errorInfo[0] == "23000")
            {
                // Duplicate key
                return -1;
            }
            else
            {
                // Unknow error
                return -100;
            }
        }
    }/*}}}*/

    public function updateTileByID($number, $length, $width, $texture, $texture_color, $class, $class_color, $img, $img_min, $series_id, $id)
    {/*{{{*/
        $sql = "update bs_tile set `number` = ?, `length` = ?, `width` = ?, `texture` = ?, `texture_color` = ?, `class` = ?, `class_color` = ?, `vip` = ?, `img` = ?, `img_min` = ?, `series_id` = ? where `id` = ?";
        $val[] = $number;
        $val[] = $length;
        $val[] = $width;
        $val[] = $texture;
        $val[] = $texture_color;
        $val[] = $class;
        $val[] = $class_color;
        $val[] = $vip;
        $val[] = $img;
        $val[] = $img_min;
        $val[] = $series_id;
        $val[] = $id;
        try
        {
            $this->_dbh->beginTrans();
            $res = $this->_dbh->execute($sql, $val); 
            $this->_dbh->commit();
            
            // 0: not change, 1 : change 1 rows
            return $res;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            if($e->errorInfo[1] == 1062 && $e->errorInfo[0] == "23000")
            {
                // Duplicate key
                return -1;
            }
            else
            {
                // Unknow error
                return -100;
            }
        }
    }/*}}}*/

    public function deleteTileByID($id)
    {/*{{{*/
        $sql = "delete from bs_tile where `id` = ?";
        $val[] = $id;
        try
        {
            $this->_dbh->beginTrans();
            $res = $this->_dbh->execute($sql, $val); 
            $this->_dbh->commit();
            
            // 0: not delete, 1 : delete 1 rows
            return $res;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            
            // Unknow error
            return -100;
        }
    }/*}}}*/

    public function sort_by_avg_price($list)
    {/*{{{*/
        uasort($list, "cb_sort_by_avg_price");            

        return $list;
    }/*}}}*/

    public function sort_by_avg_score($list)
    {/*{{{*/
        uasort($list, "cb_sort_by_avg_score");            

        return $list;
    }/*}}}*/
}

function cb_sort_by_avg_price($a, $b)
{/*{{{*/
    if($a['avg_price'] > $b['avg_price'])
    {
        return -1; 
    }
    elseif($a['avg_price'] < $b['avg_price'])
    {
        return 1;
    }
    else
    {
        return 0;
    }
}/*}}}*/

function cb_sort_by_avg_score($a, $b)
{/*{{{*/
    if($a['avg_score'] > $b['avg_score'])
    {
        return -1; 
    }
    elseif($a['avg_score'] < $b['avg_score'])
    {
        return 1;
    }
    else
    {
        return 0;
    }
}/*}}}*/


/* example 
$dbh = new Sqlexecutor('localhost', 'tile', 'tile', 'tile');
$s = new Tile($dbh);
$list = $s->getListWithAvgPriceBySeriesID();
var_dump($list);
$list = $s->sort_by_avg_price($list);
var_dump($list);
$list = $s->sort_by_avg_score($list);
var_dump($list);

$res = $s->createTile("K203031", 300, 300, "marble", "1/26920ce3876a7f26637ca25d4a026e66.jpg", "1/26920ce3876a7f26637ca25d4a026e66.jpg", 1);
var_dump($res);
$res = $s->createTile("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1);
var_dump($res);
$res = $s->getInfoByID(1);
var_dump($res);
$list = $s->getListBySeriesID(1);
var_dump($list);
$res = $s->createTile("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1);
var_dump($res);
$res = $s->updateTileByID("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 2);
var_dump($res);
$res = $s->updateTileByID("K203031", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 2);
var_dump($res);
$res = $s->updateTileByID("K203031", 400, 400, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 1);
var_dump($res);
 */
